package com.swn.common.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 功能：JDBC数据库操作类（该类不能使用单例模式）
 * 
 * @author 陶召才
 * @Date 2016年3月24日  下午1:33:01
 * @Note <这里是该类的使用注意事项>
 * 修改日期  修改人  修改内容
 * <多次修改需另起一行>
 *
 *
 */

public class DBManager {
	private static final Log log = LogFactory.getLog(DBManager.class);

	private Connection conn = null;

	private Statement query = null;

	private Statement statement = null;

	private ResultSet rs = null;

	private CallableStatement st = null;

	public DBManager() throws SQLException{
		conn = ((ComboPooledDataSource)ServiceLocator.getBean("dataSource")).getConnection();
	}



	/**
	 * 查询操作
	 * 
	 * @param sql
	 * @return rs
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public ResultSet sqlQuery(String sql) throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		if (query != null)
			query.close();
		query = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		if (sql == null)
			sql = "";
		log.info(sql.toString());
		rs = query.executeQuery(sql);
		return rs;
	}

	


	/**
	 * 获取所有结果条数
	 * 
	 * @param sql
	 * @return
	 */
	public int getRsCount(String sql) {
		int count = 0;
		StringBuffer sbr = new StringBuffer();
		sbr.append("SELECT COUNT(1) FROM (").append(sql).append(") t");
		ResultSet rst = null;
		try {
			rst = sqlQuery(sbr.toString());
			if (rst.next()) {
				count = rst.getInt(1);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		try {
			if (rst != null)
				rst.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return count;
	}

	/**
	 * 更新记录
	 * 
	 * @param sql
	 * @return
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SQLException
	 */
	public int update(String sql) throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {
		if (sql == null)
			sql = "";
		if (statement != null)
			statement.close();
		statement = conn.createStatement();
		log.info(sql.toString());
		return statement.executeUpdate(sql);
	}

	/**
	 * 添加
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public int add(String sql) throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		if (sql == null)
			sql = "";
		if (statement != null)
			statement.close();
		statement = conn.createStatement();
		log.info(sql.toString());
		return statement.executeUpdate(sql);
	}

	/**
	 * 删除
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public int delete(String sql) throws SQLException, InstantiationException,
			IllegalAccessException, ClassNotFoundException {
		if (sql == null)
			sql = "";
		if (statement != null)
			statement.close();
		statement = conn.createStatement();
		log.info(sql.toString());
		return statement.executeUpdate(sql);
	}

	/**
	 * 存贮过程调用.
	 * 
	 * @param sql
	 *            存贮过程语句
	 * @return CallableStatement
	 */
	public java.sql.CallableStatement spCall(String sql) throws Exception {
		if (sql == null) {
			sql = "";
		}
		sql = sql.trim();
		if (sql.equals("")) {
			return null;
		}
		try {
			st = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			Exception user = new Exception(e);
			log.error(user + "详细信息如下:\r\n\t" + e.getMessage() + "\r\n\t相关语句："
					+ sql);
			st = null;
			throw user;
		}
		return st;
	}

	/**
	 * 关闭连接
	 * 
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if (rs != null)
			rs.close();
		if (statement != null)
			statement.close();
		if (query != null)
			query.close();
		if (conn != null)
			conn.close();
		
	}

	/**
	 * 开启事务
	 * 
	 * @throws SQLException
	 */
	public void beginTransaction() throws SQLException {
		conn.setAutoCommit(false);
	}

	/**
	 * 关闭事务
	 * 
	 * @throws SQLException
	 */
	public void closeTransaction() throws SQLException {
		conn.commit();
	}

	/**
	 * 事务回滚
	 * 
	 * @throws SQLException
	 */
	public void rollbackTransaction() throws SQLException {
		conn.rollback();
	}

	/**
	 * 获得连接
	 * 
	 * @return
	 */
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}


}
